TRUNCATE
This lesson discusses how to delete all the rows of a table using the TRUNCATE clause.
We'll cover the following
Truncate#
In the previous lesson, we learned how to delete data using the DELETE statement. However, if we intend to delete all the rows from a table then a faster route is to use the TRUNCATE statement. Generally, we don’t want to delete all the table rows except in the case of temporary tables. The TRUNCATE statement drops a table and recreates it for faster processing. MySQL doesn’t count the number of rows affected and may show the count to be zero or non-zero, but the number doesn’t reflect the actual number of rows affected.
Example Syntax#
TRUNCATE table;
Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/14lesson.sh and wait for the MySQL prompt to start-up.
-
Execute the following query to remove all the rows from the table:
TRUNCATE Actors;
Note that TRUNCATE doesn’t work with locking or transactions and is the equivalent of DELETE when used with InnoDB tables. InnoDB refers to a particular type of database engine and is covered in the lessons ahead.
